package com.gzhryc.jdbc.dialect.h2;

import com.gzhryc.jdbc.Query;
import com.gzhryc.jdbc.dialect.AbsJdbcHelper;
import com.gzhryc.jdbc.models.Parameters;
import com.gzhryc.jdbc.utils.ResultSetUtils;

import java.sql.*;
import java.util.List;
import java.util.Map;

public class H2JdbcHelper extends AbsJdbcHelper {

    public H2JdbcHelper(String connectionKey) {
        super(connectionKey);
    }

    public <T> List<T> find(String sql, Class<T> clazz, Integer startNum,
                            Integer pageLimit) throws SQLException {
        Connection conn = null;
        try {
            conn = getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql + " limit " + startNum + "," + pageLimit);
            return ResultSetUtils.readList(pstmt.executeQuery(), clazz);
        } finally {
            //如果事自动事务则必需关闭链接
            if (conn != null && conn.getAutoCommit()) {
                conn.close();
            }
        }
    }

    public <T> List<T> find(Query query, Class<T> clazz) throws SQLException {
        Connection conn = null;
        try {
            conn = getConn();
            String sql = query.getSql();
            if (query.isPaging()) {
                sql = sql + " limit " + query.getStartNum() + "," + query.getPageLimit();
            }
            PreparedStatement pstmt = conn.prepareStatement(sql);
            query.getParams().toPreparedStatement(pstmt);
            return ResultSetUtils.readList(pstmt.executeQuery(), clazz);
        } finally {
            //如果事自动事务则必需关闭链接
            if (conn != null && conn.getAutoCommit()) {
                conn.close();
            }
        }
    }

    public List<Map<String, Object>> find(Query query) throws SQLException {
        Connection conn = null;
        try {
            conn = getConn();
            String sql = query.getSql();
            if (query.isPaging()) {
                sql = sql + " limit " + query.getStartNum() + "," + query.getPageLimit();
            }
            PreparedStatement pstmt = conn.prepareStatement(sql);
            query.getParams().toPreparedStatement(pstmt);
            return ResultSetUtils.readList(pstmt.executeQuery());
        } finally {
            //如果事自动事务则必需关闭链接
            if (conn != null && conn.getAutoCommit()) {
                conn.close();
            }
        }
    }

    public List<Map<String, Object>> find(String sql, Integer startNum,
                                          Integer pageLimit) throws SQLException {
        Connection conn = null;
        try {
            conn = getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql + " limit " + startNum + "," + pageLimit);
            return ResultSetUtils.readList(pstmt.executeQuery());
        } finally {
            //如果事自动事务则必需关闭链接
            if (conn != null && conn.getAutoCommit()) {
                conn.close();
            }
        }
    }

    public int insert(String sql, Parameters params) throws SQLException {
        Connection conn = null;
        try {
            conn = getConn();
            //获取自增长序列
            PreparedStatement pstmt = conn.prepareStatement(sql);
            params.toPreparedStatement(pstmt);
            return pstmt.executeUpdate();
        } finally {
            //如果事自动事务则必需关闭链接
            if (conn != null && conn.getAutoCommit()) {
                conn.close();
            }
        }
    }

    @Override
    public Long insertByAutoId(String sql, Parameters params)
            throws SQLException {
        Connection conn = null;
        Long id = null;
        try {
            conn = getConn();
            //获取自增长序列
            PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            params.toPreparedStatement(pstmt);
            int i = pstmt.executeUpdate();
            if (i > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    id = rs.getLong(1);
                }
                rs.close();
            }
        } finally {
            //如果事自动事务则必需关闭链接
            if (conn != null && conn.getAutoCommit()) {
                conn.close();
            }
        }
        return id;
    }

    @Override
    public <T> List<T> findJoin(String sql, Class<T> clazz, Integer startNum, Integer pageLimit) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public <T> List<T> findJoin(Query query, Class<T> clazz) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
}
